# 设置MySQL参数
MYSQL_USER="ywzt"
MYSQL_PASSWORD="Fjywzt@)@!server2021"
MYSQL_HOST="rm-6zsw529hs7y4y6appyo.mysql.rds.ops.cloud.fjtic.cn"
BACKUP_DIR="/data/mysqlbackup"
MYSQL_PORT="3307"
CURRENT_TIME=$(date +"%Y-%m-%d_%H%M%S")
NUM=${1:-1440}
EXP="${2:-*.gz}"

echo "---------------------START-----------------------" >> /data/mysqlbackup/log/dump.log
echo "备份数据库脚本开始,时间 ${CURRENT_TIME}" >> /data/mysqlbackup/log/dump.log
# 连接MySQL，获取所有数据库名称
DATABASES=$(mysql --user=${MYSQL_USER} --password=${MYSQL_PASSWORD} --port=${MYSQL_PORT} --host=${MYSQL_HOST} -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema)")
# 遍历所有数据库，分别备份
for DB_NAME in $DATABASES; do
  # 备份数据库
  echo "开始备份数据库${DB_NAME}" >> /data/mysqlbackup/log/dump.log
  mysqldump  --skip-opt --single-transaction --user=${MYSQL_USER} --password=${MYSQL_PASSWORD} --port=${MYSQL_PORT} --host=${MYSQL_HOST} ${DB_NAME} |gzip > ${BACKUP_DIR}/${DB_NAME}-${CURRENT_TIME}.sql.gz
done
echo   "所有数据库备份完成,时间 `date +%Y-%m-%d_%H%M%S` " >> /data/mysqlbackup/log/dump.log
echo   "准备删除${NUM}分钟之前,符合${EXP}的文件" >> /data/mysqlbackup/log/dump.log
find ${BACKUP_DIR} -type f -name "${EXP}" -mmin +${NUM}  -delete  -printf "删除文件: %p\n" >> /data/mysqlbackup/log/dump.log
echo "----------------------END-------------------------" >> /data/mysqlbackup/log/dump.log


#---------------------------------------------------版本2,会创建一个基于当前时间的文件夹,然后把备份文件全都放到文件夹中---------------------------------------------
# 设置MySQL参数
MYSQL_USER="ywzt"
MYSQL_PASSWORD="Fjywzt@)@!server2021"
MYSQL_HOST="rm-6zsw529hs7y4y6appyo.mysql.rds.ops.cloud.fjtic.cn"
BACKUP_DIR="/data/mysqlbackup"
MYSQL_PORT="3307"
CURRENT_TIME=$(date +"%Y-%m-%d_%H%M%S")
BACKUP_FILES_DIR="${BACKUP_DIR}/backupFiles_${CURRENT_TIME}"
NUM=${1:-1440}
EXP="${2:-*.gz}"

echo "---------------------START-----------------------" >> /data/mysqlbackup/log/dump.log
mkdir -p ${BACKUP_FILES_DIR}
echo "备份数据库脚本开始,时间 ${CURRENT_TIME}" >> /data/mysqlbackup/log/dump.log
# 连接MySQL，获取所有数据库名称
DATABASES=$(mysql --user=${MYSQL_USER} --password=${MYSQL_PASSWORD} --port=${MYSQL_PORT} --host=${MYSQL_HOST} -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema)")
# 遍历所有数据库，分别备份
for DB_NAME in $DATABASES; do
  # 备份数据库
  echo "开始备份数据库${DB_NAME}" >> /data/mysqlbackup/log/dump.log
  mysqldump  --skip-opt --single-transaction --user=${MYSQL_USER} --password=${MYSQL_PASSWORD} --port=${MYSQL_PORT} --host=${MYSQL_HOST} ${DB_NAME} |gzip > ${BACKUP_FILES_DIR}/${DB_NAME}-${CURRENT_TIME}.sql.gz
done
echo   "所有数据库备份完成,时间 `date +%Y-%m-%d_%H%M%S` " >> /data/mysqlbackup/log/dump.log
#echo   "准备删除${NUM}分钟之前,符合${EXP}的文件" >> /data/mysqlbackup/log/dump.log
#find ${BACKUP_DIR} -type f -name "${EXP}" -mmin +${NUM}  -delete  -printf "删除文件: %p\n" >> /data/mysqlbackup/log/dump.log
echo   "准备删除${NUM}分钟之前,符合${BACKUP_DIR}/backupFiles_*的文件夹" >> /data/mysqlbackup/log/dump.log
find ${BACKUP_DIR} -type d -name "backupFiles_*" -mmin +${NUM} -exec rm -rf {} \; -printf "删除文件: %p\n" >> /data/mysqlbackup/log/dump.log
echo "----------------------END-------------------------" >> /data/mysqlbackup/log/dump.log